alter table caseoutlet add co_pos_form CLOB;
alter table caseoutlet add co_installation clob;

alter table
   caseoutlet
add
   (
   co_esapl clob null,  
   co_ezy clob null
   );
   
alter table
   outlet
add
   (
   o_esapl clob null,  
   o_ezy clob null
   );
   
   
alter table caseoutlet modify (
	co_case_id number(16) not null,
	co_outlet_id number(16) not null
);

alter table CASEOUTLET add constraint CASEOUTLET_PK primary key(CO_CASE_ID, CO_OUTLET_ID);

CREATE TABLE CASEINSTALLNOTE
   (	"IN_ID" NUMBER(16,0) NOT NULL, 
	"IN_CASE_ID" NUMBER(16,0) NOT NULL, 
  "IN_OUTLET_ID" NUMBER(16,0) NOT NULL, 
	"IN_CREATED_BY" VARCHAR2(100 BYTE), 
	"IN_CREATED_DATE" DATE, 
	"IN_REMARK" VARCHAR2(4000 BYTE), 
	"IN_STATUS" VARCHAR2(20 BYTE), 
	"IN_RESCHEDULE_DATE" VARCHAR2(20 BYTE), 
	 CONSTRAINT "CASEINSTALLNOTE_PK" PRIMARY KEY ("IN_ID")
   );
   
CREATE INDEX CASEINSTALLNOTE_INDEX1 ON CASEINSTALLNOTE (IN_CASE_ID, IN_OUTLET_ID);
CREATE SEQUENCE SEQ_CASEINSTALLNOTE INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER TRIGGER_CASEINSTALLNOTE_INS 
BEFORE INSERT ON CASEINSTALLNOTE FOR EACH ROW  
DECLARE
  tempId CASEINSTALLNOTE.IN_ID%TYPE;
BEGIN
  select SEQ_CASEINSTALLNOTE.nextval into tempId from dual;  
  :NEW.IN_ID := tempId;
END;

CREATE TABLE ifmcc
( 
  ifm_code varchar2(20) not null,
  ifm_ifid number null,
  
  CONSTRAINT ifmcc_pk PRIMARY KEY (ifm_code)
);

CREATE TABLE ifdetails
( 
  IF_ID	NUMBER	not null,
  IF_TYPE	VARCHAR2(100 BYTE)	null,
  IF_VLN	NUMBER	null,
  IF_VLH	NUMBER	null,
  IF_VLM	NUMBER	null,
  IF_VLEC	NUMBER	null,
  IF_VFN	NUMBER	null,
  IF_VFH	NUMBER	null,
  IF_VFM	NUMBER	null,
  IF_VFEC	NUMBER	null,
  IF_MCLN	NUMBER	null,
  IF_MCLH	NUMBER	null,
  IF_MCLM	NUMBER	null,
  IF_MCLEC	NUMBER	null,
  IF_MCFN	NUMBER	null,
  IF_MCFH	NUMBER	null,
  IF_MCFM	NUMBER	null,
  IF_MCFEC	NUMBER	null,
  CONSTRAINT ifdetails_pk PRIMARY KEY (if_id)
);

REM INSERTING into IFMCC
Insert into IFMCC (IFM_CODE,IFM_IFID) values (5499,2);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (5541,3);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (5542,3);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (6300,4);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (5960,4);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (9311,5);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (9399,5);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (9222,5);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (5411,6);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8398,7);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8661,7);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (4900,8);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (4121,9);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (6530,9);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (7523,9);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (7832,9);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8211,10);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8220,10);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8241,10);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8244,10);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8249,10);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8299,10);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (5814,11);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (8351,12);
Insert into IFMCC (IFM_CODE,IFM_IFID) values (0,1);


REM INSERTING into IFDETAILS
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (1,'default',1.35,1.8,1.8,1.44,1.5,2.12,1.58,1.44,1.34,1.65,1.41,1.34,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (2,'conv_store',1,1,1,1,1.5,2.12,1.58,1.44,0.9,0.9,0.9,0.9,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (3,'petrol',0.9,0.9,0.9,0.9,1.5,2.12,1.58,1.44,0.9,0.9,0.9,0.9,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (4,'insurance',1,1,1,1,1.5,2.12,1.58,1.44,1,1,1,1,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (5,'government',0.5,0.5,0.5,0.5,1.5,2.12,1.58,1.44,0.5,0.5,0.5,0.5,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (6,'supermarket',1,1,1,1,1.5,2.12,1.58,1.44,1,1,1,1,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (7,'charities',1.35,1.35,1.35,1.35,1.5,2.12,1.58,1.44,0,0,0,0,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (8,'utilities',0.8,0.8,0.8,0.8,1.5,2.12,1.58,1.44,0.5,0.5,0.5,0.5,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (9,'emer_market',1.35,1.8,1.8,1.44,1.5,2.12,1.58,1.44,0.9,0.9,0.9,0.9,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (10,'education',1,1,1,1,1.5,2.12,1.58,1.44,0.9,0.9,0.9,0.9,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (11,'fast_food',1,1,1,1,1.5,2.12,1.58,1.44,0.5,0.5,0.5,0.5,1.53,1.67,1.69,1.53);
Insert into IFDETAILS (IF_ID,IF_TYPE,IF_VLN,IF_VLH,IF_VLM,IF_VLEC,IF_VFN,IF_VFH,IF_VFM,IF_VFEC,IF_MCLN,IF_MCLH,IF_MCLM,IF_MCLEC,IF_MCFN,IF_MCFH,IF_MCFM,IF_MCFEC) values (12,'child_care',0.8,0.8,0.8,0.8,1.5,2.12,1.58,1.44,0.9,0.9,0.9,0.9,1.53,1.67,1.69,1.53);


alter table casehist add ch_assigned_to VARCHAR2(4000);

CREATE INDEX CASEHIST_INDEX_CASEID ON CASEHIST (CH_CASE_ID);

CREATE INDEX CASEDOC_INDEX_CASEID ON CASEDOC (CD_CASE_ID);

alter table notification modify n_id number(16) not null;
alter table notification add constraint NOTIFICATION_PK primary key(n_id);
alter table notification add (
	n_hist_id number(16),
	n_status varchar2(20) default 'Active',
	n_modified_date date,
	n_modified_by varchar2(100)
);

CREATE INDEX notification_index1 ON notification (N_USER_ID, N_CASE_ID, N_HIST_ID);

update notification set n_modified_by = n_created_by, n_modified_date = n_created_date;


CREATE TABLE CASEMATCHTRACE
(	"CM_ID" NUMBER(16,0) NOT NULL, 
"CM_CASE_ID" NUMBER(16,0) NOT NULL, 
"CM_DATA" CLOB NOT NULL, 
"CM_STATUS" VARCHAR2(20 BYTE), 
"CM_CREATED_BY" VARCHAR2(100 BYTE), 
"CM_CREATED_DATE" DATE, 
"CM_MODIFIED_BY" VARCHAR2(100 BYTE), 
"CM_MODIFIED_DATE" DATE, 
 CONSTRAINT "CASEMATCHTRACE_PK" PRIMARY KEY ("CM_ID")
);

CREATE INDEX CASEMATCHTRACE_INDEX_CASEID ON CASEMATCHTRACE (CM_CASE_ID);

CREATE SEQUENCE SEQ_CASEMATCHTRACE INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER TRIGGER_CASEMATCHTRACE_INS 
BEFORE INSERT ON CASEMATCHTRACE FOR EACH ROW 
DECLARE
  tempId CASEMATCHTRACE.CM_ID%TYPE;
BEGIN
  select SEQ_CASEMATCHTRACE.nextval into tempId from dual;  
  :NEW.CM_ID := tempId;
END;


alter table casemast add c_document_checklist clob;


create or replace PACKAGE STATUSACTION AS 

  FUNCTION action_next(case_id NUMBER, action VARCHAR2, note IN VARCHAR2, assigned_to in VARCHAR2, user_id VARCHAR2) RETURN NUMBER;

END STATUSACTION;


create or replace PACKAGE BODY STATUSACTION AS

  FUNCTION action_next(case_id NUMBER, action VARCHAR2, note IN VARCHAR2, assigned_to in VARCHAR2, user_id VARCHAR2) RETURN NUMBER
  AS 
    current_status STATUSMAST.S_CURRENT%TYPE;
    next_status STATUSMAST.S_NEXT%TYPE;
    nullable_note CASEHIST.CH_NOTE%TYPE;
    nullable_assigned_to CASEHIST.CH_ASSIGNED_TO%TYPE;
    hist_id CASEHIST.CH_ID%TYPE;
  BEGIN 
    
    nullable_note := note;
    if length(note) <= 0 then
      nullable_note := null;
    end if;
    
    nullable_assigned_to := assigned_to;
    if length(assigned_to) <= 0 then
      nullable_assigned_to := null;
    end if;
    
    select c_status into current_status 
    from casemast
    where c_id = case_id;
    
    select s_next into next_status from statusmast
    where (s_current = current_status or s_current = '*')
      and s_action = action;
    
    if next_status = '*' then
      next_status := current_status;
    end if;
    
    insert into casehist(ch_case_id, ch_action, ch_status, ch_last_status, 
      ch_note, ch_assigned_to, ch_created_date, ch_created_by)
    values(case_id, action, next_status, current_status,
      nullable_note, nullable_assigned_to, sysdate, user_id);
    
    select seq_casehist.currval into hist_id from dual;
    
    update casemast set c_action = action,
      c_status = next_status,
      c_modified_date = sysdate,
      c_modified_by = user_id
    where c_id = case_id;
    
    if current_status = 'PUT UP' then
      update casemast set c_submit_date = sysdate
      where c_id = case_id
        and c_submit_date is null;
    end if;
    
    return hist_id;
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return -1;
  END;

END STATUSACTION;